package com.new2i.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.ResourceBundle;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

/**
 * 数据库操作类，请结合apache-commons-dbutils 使用
 * 
 * @author liyuhong
 *
 */
public class Database {

	Connection conn;

	/**
	 * 构造函数
	 */
	public Database() {

		this(null);
	}

	/**
	 * 根据配置文件名获取数据连接
	 * 
	 * @param dbConfName
	 *            配置文件名（包含路径）
	 */
	public Database(String dbConfName) {

		if (dbConfName == null)
			dbConfName = "dbconn";// 默认oracle数据库
		ResourceBundle resConf = ResourceBundle.getBundle(dbConfName);// 获取数据库配置文件
		// System.out.println("user:"+resConf.getString("db.user"));
		DbUtils.loadDriver(resConf.getString("db.driver"));

		try {
			Properties prop = new Properties();
			prop.setProperty("user", resConf.getString("db.user"));
			prop.setProperty("password", resConf.getString("db.password"));
			// 获取数据库连接
			this.conn = DriverManager.getConnection(resConf.getString("db.url"), prop);
			// System.out.println("conn:"+this.conn);
		} catch (SQLException e) {
			System.out.print(new Date());
			System.out.println(":数据驱动连接失败");
		}

	}

	/**
	 * 根据sql语句查询
	 * 
	 * @param sql
	 *            SQL语句
	 * @return 返回List<?><Map<String,Object>>对象
	 */
	public List<?> query(String sql) {

		return (List<?>) query(sql, 100);
	}

	/**
	 * 根据sql语句查询
	 * 
	 * @param sql
	 *            SQL语句
	 * @param mapOrList
	 *            返回类型 0：返回单条数据Map<String,Object>类型 ,其他数字：返回List<?><Map
	 *            <String,Object>>类型
	 * @return 返回查询数据
	 */
	public Object query(String sql, int mapOrList) {

		return this.query(sql, null, mapOrList);

	}

	/**
	 * 根据sql语句查询
	 * 
	 * @param sql
	 *            SQL语句
	 * @param parms
	 *            传入的预处理参数,sql预处理
	 * @param mapOrList
	 *            返回类型 0：返回单条数据Map<String,Object>类型 ,其他数字：返回List<?><Map
	 *            <String,Object>>类型
	 * @return 返回查询数据
	 */
	public Object query(String sql, Object[] parms, int mapOrList) {

		if (mapOrList == 0) {
			return this.query(sql, parms, 0, null);
		} else
			return this.query(sql, parms, 100, null);

	}

	/**
	 * 数据库查询
	 * 
	 * @param sql
	 *            SQL语句
	 * @param parms
	 *            查询参数，没有参数则为null
	 * @param mapOrListOrBeanOrBeanlist
	 *            查询类型 0：Map类型； 10：Bean类型； 20：List<?><Bean>类型； 其他：返回List
	 *            <?><Map>类型
	 * @param type
	 *            bean class对象
	 * @return 返回查询数据
	 */
	@SuppressWarnings("deprecation")
	public Object query(String sql, Object[] parms, int mapOrListOrBeanOrBeanlist, Class<?> type) {
		QueryRunner qrun = new QueryRunner();

		try {
			if (mapOrListOrBeanOrBeanlist == 0) {
				// 返回 Map 对象
				return (Map<?, ?>) qrun.query(this.conn, sql, parms, new MapHandler());
			} else if (mapOrListOrBeanOrBeanlist == 10) {
				// 返回 bean 对象
				return qrun.query(this.conn, sql, parms, new BeanHandler<>(type));
			} else if (mapOrListOrBeanOrBeanlist == 20) {
				// 返回 List<?><bean> 对象
				return qrun.query(this.conn, sql, parms, new BeanListHandler<>(type));
			} else {
				// 返回List<?><Map>对象
				return (List<?>) qrun.query(this.conn, sql, parms, new MapListHandler());
			}
		} catch (SQLException e) {

			// 发生异常关闭数据库连接
			this.close();

			e.printStackTrace();

			System.out.println("sql:" + sql);
			System.out.println("Object[]:");
			int i = 0;
			do {
				System.out.println("i:" + String.valueOf(parms[i]));

				i++;
				if (parms == null)
					break;
			} while (i < parms.length);

		}

		return null;
	}

	/**
	 * 插入更新数据库
	 * 
	 * @param sql
	 *            插入或更新SQL
	 * @return 成功返回true，失败返回false
	 */
	public boolean update(String sql) {
		QueryRunner qrun = new QueryRunner();
		try {
			qrun.update(this.conn, sql);
		} catch (SQLException e) {

			// 发生异常关闭数据库连接
			this.close();

			System.out.println(e.getMessage());

			return false;
		}

		return true;
	}

	/**
	 * 预处理更新
	 * 
	 * @param sql
	 *            预处理SQL
	 * @param obj
	 *            传入的预处理参数
	 * @return 成功返回true，失败返回false
	 */
	public boolean update(String sql, Object[] obj) {
		QueryRunner qrun = new QueryRunner();
		try {
			qrun.update(this.conn, sql, obj);
		} catch (SQLException e) {

			// 发生异常关闭数据库连接
			this.close();

			System.out.println(e.getMessage());

			return false;
		}

		return true;
	}

	public boolean update(String sql, Object obj) {
		QueryRunner qrun = new QueryRunner();
		try {
			qrun.update(this.conn, sql, obj);
		} catch (SQLException e) {
			// 发生异常关闭数据库连接
			this.close();

			System.out.println(e.getMessage());

			return false;
		}

		return true;
	}

	/**
	 * 返回主键的update
	 * 
	 * @param sql
	 * @return 主键
	 */
	public int jdbcUpdate(String sql, Object[] objData) {

		if (objData != null) {
			String[] strArr = new String[objData.length];

			for (int i = 0; i < strArr.length; i++) {
				strArr[i] = this.convertSqlString(objData[i]);
			}

			sql = SqlCustomUtils.insertVal(sql, strArr);
		}
		System.out.println("sql:" + sql);
		try {
			Statement stmt = this.conn.createStatement();
			stmt.execute(sql, Statement.RETURN_GENERATED_KEYS);
			ResultSet rst = stmt.getGeneratedKeys();
			if (rst.next()) {
				// System.out.println("user_id:"+rst.getInt(1));
				return rst.getInt(1);
			}
			stmt.close();

		} catch (SQLException e) {
			this.close();
			e.printStackTrace();
		}

		return 0;
	}

	public boolean batchUpdate(String[] batchSql) {
		boolean success = false;
		Statement smt1 = null;
		try {
			this.conn.setAutoCommit(false);

			smt1 = this.conn.createStatement();
			for (int i = 0; i < batchSql.length; i++) {
				// System.out.println(batchSql[i]);
				smt1.addBatch(batchSql[i]);
			}

			smt1.executeBatch();
			this.conn.commit();
			success = true;
		} catch (SQLException e) {
			try {
				this.conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			try {
				this.conn.setAutoCommit(true);

				if (smt1 != null)
					DbUtils.close(smt1);
			} catch (SQLException e3) {
				e3.printStackTrace();
			}
		} finally {
			try {
				this.conn.setAutoCommit(true);

				if (smt1 != null)
					DbUtils.close(smt1);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return success;
	}

	/**
	 * 批量事务预处理
	 * 
	 * @param batchSql
	 *            sql语句数组
	 * @param objs
	 *            相对应sql数组的
	 * @return
	 */
	public boolean batchUpdate(List<String> batchSql, List<Object[]> objs) {
		boolean success = true;

		ArrayList<String> arrSql = new ArrayList<>();

		for (int i = 0; i < batchSql.size(); i++) {

			Object[] objData = objs.get(i);

			if (objData != null) {
				String[] strArr = new String[objData.length];

				for (int k = 0; k < strArr.length; k++) {
					strArr[k] = this.convertSqlString(objData[k]);
				}
				// System.out.println( SqlCustomUtils.insertVal(batchSql.get(i),
				// strArr));
				arrSql.add(SqlCustomUtils.insertVal(batchSql.get(i), strArr));
			} else {
				arrSql.add(batchSql.get(i));
			}
			// System.out.println( SqlCustomUtils.insertVal(batchSql.get(i),
			// strArr));
		}

		boolean result = batchUpdate(arrSql);

		if (result)
			success = true;

		return success;
	}

	/**
	 * 批量处理SQL操作
	 * 
	 * @param sqlArrList<?>
	 *            SQL
	 * @return
	 */
	public boolean batchUpdate(ArrayList<String> sqlArrList) {
		String[] str = new String[sqlArrList.size()];
		sqlArrList.toArray(str);
		return batchUpdate(str);
	}

	@SuppressWarnings("unchecked")
	public <T> T QueryOne(Class<T> entityClass, String sql) {
		QueryRunner queryRunner = new QueryRunner();
		Object object = null;
		try {
			object = queryRunner.query(this.conn, sql, new BeanHandler<>(entityClass));
		} catch (SQLException e) {
			e.printStackTrace();
			this.close();
		}
		return (T) object;
	}

	/**
	 */
	/**
	 * 关闭数据库连接
	 */
	public void close() {
		DbUtils.closeQuietly(this.conn);
	}

	/**
	 * 获得数据库连接对象
	 * 
	 * @return
	 */
	public Connection getConn() {
		return this.conn;
	}

	/**
	 * 转换对象为sql形式的字符串
	 * 
	 * @param obj
	 * @return
	 */
	private String convertSqlString(Object obj) {

		if (obj == null) {
			return "null";
		} else if (obj instanceof String) {
			return "'" + ((String) obj).replaceAll("'", "\"") + "'";
		} else if (obj instanceof Integer) {
			return String.valueOf(((Integer) obj).intValue());
		} else if (obj instanceof Float) {
			return String.valueOf(((Float) obj).floatValue());
		} else if (obj instanceof Double) {
			return String.valueOf(((Double) obj).doubleValue());
		} else if (obj instanceof java.lang.Long) {
			return String.valueOf(((java.lang.Long) obj).longValue());
		} else if (obj instanceof java.math.BigDecimal) {
			return String.valueOf(((java.math.BigDecimal) obj).doubleValue());
		} else if (obj instanceof java.util.Date) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String date = sdf.format((Date) obj);
			// return "to_date('"+sdf.format((Date)obj)+"','yyyy-mm-dd
			// hh24:mi:ss')";
			return "STR_TO_DATE('" + date + "','%Y-%m-%d %H:%i:%s')";
		} else {
			return "null";
		}

	}

	@SuppressWarnings("unchecked")
	public static void main(String[] args) {

		Database db = new Database();
		String sql = "select * from student where sclass_id=6 and student_name like '%李%' order by create_date desc";
		Map<String, Object> map = (Map<String, Object>) db.query(sql, 0);
		System.out.println("sss:" + map.get("student_name"));
		db.close();

	}

}
